﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;

namespace Trying1.Classes
{
    class RoomDBManager
    {
        
        public static ArrayList GetAll()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room m = new Room();
                    m.roomNum = (int)dr["roomNum"];
                    m.roomType = (string)dr["roomType"];
                    m.roomStatus = (string)dr["roomStatus"];
                    m.roomRate = (decimal)dr["roomRate"];
                    m.roomImgUrl = (string)dr["roomImgUrl"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static ArrayList GetAll1()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room m = new Room();
                    m.roomNum = (int)dr["roomNum"];
                    m.roomType = (string)dr["roomType"];
                    m.roomStatus = (string)dr["roomStatus"];
                    m.roomRate = (decimal)dr["roomRate"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList GetRoomByRoomType(string roomType)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomNum,roomType,roomStatus,roomRate FROM Room WHERE roomType=@roomType";
                comm.Parameters.AddWithValue("@roomType", roomType);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room m = new Room();
                    m.roomNum = (int)dr["roomNum"];
                    m.roomType = (string)dr["roomType"];
                    m.roomStatus = (string)dr["roomStatus"];
                    m.roomRate = (decimal)dr["roomRate"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        
        public static ArrayList GetRoomByVacant()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE roomStatus='Vacant'";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room m = new Room();
                    m.roomNum = (int)dr["roomNum"];
                    m.roomType = (string)dr["roomType"];
                    m.roomStatus = (string)dr["roomStatus"];
                    m.roomRate = (decimal)dr["roomRate"];
                    m.roomImgUrl = (string)dr["roomImgUrl"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        
        public static Room GetRoomByRoomNum(int roomNum)
        {
            Room m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Room();
                    m.roomNum = (int)dr["roomNum"];
                    m.roomType = (string)dr["roomType"];
                    m.roomStatus = (string)dr["roomStatus"];
                    m.roomRate = (decimal)dr["roomRate"];
                    m.roomImgUrl = (string)dr["roomImgUrl"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static int UpdateGuestCheckIn(int roomNum,string addReq,DateTime checkOutDate,int guestNo)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Update Room SET Room.roomStatus='Occupied' where Room.roomNum=@roomNum;Update Booking SET addReq=@addReq, checkOutDate=@checkOutDate where guestNo=@guestNo; ";
                comm.Parameters.AddWithValue("@roomNum",roomNum );
                comm.Parameters.AddWithValue("@addReq", addReq);
                comm.Parameters.AddWithValue("@checkOutDate", checkOutDate);
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
       
    }
    
}
